package com.jxm.jsqlparser;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.util.SelectUtils;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.compress.utils.Sets;
import org.junit.Test;

import java.io.StringReader;
import java.util.Arrays;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * @Author: jxm
 * @Description:
 * expression：SQL构建相关类，比如EqualsTo、InExpression等表达式用于构建SQL。
 * parser： SQL解析相关类，比如CCJSqlParserUtil。
 * schema：主要存放数据库schema相关的类 ，比如表、列等。
 * statement：封装了数据库操作对象，create、insert、delete、select等
 * util： 各种工具类、不同DB版本、SQL标准等处理类，如SelectUtils、DatabaseType等。
 *
 * @Date: 2023-12-25 10:59
 * @Version: 1.0
 */
public class Test1 {


    //Insert 解析
    @Test
    public void testInsertParser() throws JSQLParserException {
        String insertSql = "INSERT INTO test (c1,c2) VALUES ('001','002')";
        Statement statement = CCJSqlParserUtil.parse(insertSql);
        if (statement instanceof Insert){
            Insert insert = (Insert) statement;
            //添加新列
            insert.addColumns(new Column("c3"));
            // 添加新插入值
            ExpressionList expressionList = (ExpressionList)insert.getItemsList();
            expressionList.getExpressions().add(new StringValue("003"));
            System.out.println(insert);
        }
    }

    /**
     * Update 解析
     *
     * @throws JSQLParserException
     */
    @Test
    public void testUpdateParser() throws JSQLParserException {
        String updateSql = "UPDATE test SET c1 = '001' WHERE c2 = '003' ";
        Statement statement = CCJSqlParserUtil.parse(updateSql);
        if(statement instanceof Update){
            Update update = (Update) statement;
            Expression where = update.getWhere(); // 获取WHERE表达式
            System.err.println(where);
            List<Column> columns = update.getColumns(); // 获取修改列
            columns.forEach(System.out::println);
        }
    }

    @Test
    //解析查询
    public void testSelectParser() throws JSQLParserException {
        String SQL002 = "SELECT t1.a , t1.b  FROM tab1 AS t1 JOIN tab2 t2 ON t1.user_id  = t2.user_id";   // 多表SQL
        // 1.解析表名
        CCJSqlParserManager parserManager = new CCJSqlParserManager();
        // 解析SQL为Statement对象
        Statement statement = null;
        statement = parserManager.parse(new StringReader(SQL002));
        // 创建表名发现者对象
        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        // 获取到表名列表
        List<String> tableNameList = tablesNamesFinder.getTableList(statement);
        if (tableNameList != null) {
            tableNameList.forEach(System.err::println); // 循环打印解析到的表名 tab1 tab2
        }

        // 2.解析查询元素   列，函数等
        Select select = null;
        select = (Select) CCJSqlParserUtil.parse(SQL002);
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        List<SelectItem> selectItems = plainSelect.getSelectItems();
        selectItems.forEach(System.err::println); // t1.a , t1.b
        // 3.解析WHERE条件
        String SQL_WHERE = "SELECT *  FROM tableName WHERE ID = 8";
        PlainSelect plainSelectWhere = null;
        Select select1 = (Select)CCJSqlParserUtil.parse(SQL_WHERE);
        SelectBody selectBody = select1.getSelectBody();
        plainSelectWhere = (PlainSelect) selectBody;
        EqualsTo equalsTo = (EqualsTo)plainSelectWhere.getWhere();
        Expression leftExpression = equalsTo.getLeftExpression();
        Expression rightExpression = equalsTo.getRightExpression();
        System.out.println();
        System.err.println(leftExpression); // ID
        System.err.println(rightExpression); // 8
        // 4.解析Join
        System.out.println();
        List<Join> joins = plainSelect.getJoins();
        joins.forEach(e -> {
            Expression onExpression = e.getOnExpression();
            System.err.println(onExpression); // 获取ON 表达式 t1.user_id = t2.user_id
        });
        System.out.println();
        // 5.解析IN
        String SQL_IN = "SELECT *  FROM tableName WHERE ID IN (8,9,10)";
        PlainSelect plainSelectIn = (PlainSelect) ((Select) CCJSqlParserUtil.parse(SQL_IN)).getSelectBody();
        InExpression inExpression = (InExpression) plainSelectIn.getWhere();
        Expression leftExpression1 = inExpression.getLeftExpression();
        ItemsList rightItemsList = inExpression.getRightItemsList();
        System.err.println(leftExpression1);// ID
        System.err.println(rightItemsList); // (8, 9, 10)

    }

    /**
     * 构建插入语句
     */
    @Test
    public void buildInsertSql() {
        // 创建表对象设置表名
        Table table = new Table();
        table.setName("table");

        // 创建插入对象
        Insert insert = new Insert();
        insert.setTable(table); // 设置插入对象的表对象

        // 设置插入列
        List<Column> columnList = Arrays.asList(new Column("col1"), new Column("col2"));
        insert.setColumns(columnList);

        // 设置插入值
        MultiExpressionList multiExpressionList = new MultiExpressionList();
        multiExpressionList.addExpressionList(Arrays.asList(new StringValue("1"), new StringValue("2")));
        insert.setItemsList(multiExpressionList);

        System.err.println(insert); // INSERT INTO table (col1, col2) VALUES ('1', '2')
    }

    /**
     * 单表SQL查询
     *
     * @throws JSQLParserException
     */
    @Test
    public void testSelectOneTable() throws JSQLParserException {
        // 单表全量
        Table table = new Table("test");
        Select select = SelectUtils.buildSelectFromTable(table);
        System.err.println(select); // SELECT * FROM test

        // 指定列查询
        Select buildSelectFromTableAndExpressions = SelectUtils.buildSelectFromTableAndExpressions(new Table("test"), new Column("col1"), new Column("col2"));
        System.err.println(buildSelectFromTableAndExpressions); // SELECT col1, col2 FROM test

        // WHERE =
        EqualsTo equalsTo = new EqualsTo(); // 等于表达式
        equalsTo.setLeftExpression(new Column(table, "user_id")); // 设置表达式左边值
        equalsTo.setRightExpression(new StringValue("123456"));// 设置表达式右边值
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody(); // 转换为更细化的Select对象
        plainSelect.setWhere(equalsTo);
        System.err.println(plainSelect);//  SELECT * FROM test WHERE test.user_id = '123456'

        // WHERE  != <>
        NotEqualsTo notEqualsTo = new NotEqualsTo();
        notEqualsTo.setLeftExpression(new Column(table, "user_id")); // 设置表达式左边值
        notEqualsTo.setRightExpression(new StringValue("123456"));// 设置表达式右边值
        PlainSelect plainSelectNot = (PlainSelect) select.getSelectBody();
        plainSelectNot.setWhere(notEqualsTo);
        System.err.println(plainSelectNot);//  SELECT * FROM test WHERE test.user_id <> '123456'

        // 其他运算符, 参考上面代码添加表达式即可
        GreaterThan gt = new GreaterThan(); // ">"
        GreaterThanEquals geq = new GreaterThanEquals(); // ">="
        MinorThan mt = new MinorThan(); // "<"
        MinorThanEquals leq = new MinorThanEquals();// "<="
        IsNullExpression isNull = new IsNullExpression(); // "is null"
        isNull.setNot(true);// "is not null"
        LikeExpression nlike = new LikeExpression();
        nlike.setNot(true); // "not like"
        Between bt = new Between();
        bt.setNot(true);// "not between"

        // WHERE LIKE
        LikeExpression likeExpression = new LikeExpression(); // 创建Like表达式对象
        likeExpression.setLeftExpression(new Column("username")); // 表达式左边
        likeExpression.setRightExpression(new StringValue("张%")); // 右边表达式
        PlainSelect plainSelectLike = (PlainSelect) select.getSelectBody();
        plainSelectLike.setWhere(likeExpression);
        System.err.println(plainSelectLike); // SELECT * FROM test WHERE username LIKE '张%'

        // WHERE IN
        Set<String> deptIds = Sets.newHashSet(); // 创建IN范围的元素集合
        deptIds.add("0001");
        deptIds.add("0002");
        ItemsList itemsList = new ExpressionList(deptIds.stream().map(StringValue::new).collect(Collectors.toList())); // 把集合转变为JSQLParser需要的元素列表
        InExpression inExpression = new InExpression(new Column("dept_id "), itemsList); // 创建IN表达式对象，传入列名及IN范围列表
        PlainSelect plainSelectIn = (PlainSelect) select.getSelectBody();
        plainSelectIn.setWhere(inExpression);
        System.err.println(plainSelectIn); // SELECT * FROM test WHERE dept_id  IN ('0001', '0002')

        // WHERE BETWEEN AND
        Between between = new Between();
        between.setBetweenExpressionStart(new LongValue(18)); // 设置起点值
        between.setBetweenExpressionEnd(new LongValue(30)); // 设置终点值
        between.setLeftExpression(new Column("age")); // 设置左边的表达式，一般为列
        PlainSelect plainSelectBetween = (PlainSelect) select.getSelectBody();
        plainSelectBetween.setWhere(between);
        System.err.println(plainSelectBetween); // SELECT * FROM test WHERE age BETWEEN 18 AND 30

        //  WHERE AND 多个条件结合,都需要成立
        AndExpression andExpression = new AndExpression(); // AND 表达式
        andExpression.setLeftExpression(equalsTo); // AND 左边表达式
        andExpression.setRightExpression(between);  // AND 右边表达式
        PlainSelect plainSelectAnd = (PlainSelect) select.getSelectBody();
        plainSelectAnd.setWhere(andExpression);
        System.err.println(plainSelectAnd); //  SELECT * FROM test WHERE test.user_id = '123456' AND age BETWEEN 18 AND 30

        //  WHERE OR 多个条件满足一个条件成立返回
        OrExpression orExpression = new OrExpression();// OR 表达式
        orExpression.setLeftExpression(equalsTo); // OR 左边表达式
        orExpression.setRightExpression(between);  // OR 右边表达式
        PlainSelect plainSelectOr = (PlainSelect) select.getSelectBody();
        plainSelectOr.setWhere(orExpression);
        System.err.println(plainSelectOr); // SELECT * FROM test WHERE test.user_id = '123456' OR age BETWEEN 18 AND 30

        // ORDER BY 排序
        OrderByElement orderByElement = new OrderByElement(); // 创建排序对象
        orderByElement.isAsc(); //  设置升序排列 从小到大
        orderByElement.setExpression(new Column("col01")); // 设置排序字段
        PlainSelect plainSelectOrderBy = (PlainSelect) select.getSelectBody();
        plainSelectOrderBy.addOrderByElements(orderByElement);
        System.err.println(plainSelectOrderBy); // SELECT * FROM test WHERE test.user_id = '123456' OR age BETWEEN 18 AND 30 ORDER BY col01
    }



}
